The present invention relates generally to the field of database indexes, and more particularly to masking database indexes to optimize data insertion speed.
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indexes usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), yielding the possibility to store indexes in memory for a table whose data is too large to store in memory.
In a relational database, an index is a copy of one part of a table. Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. For example, an index could be created on upper (last_name), which would only store the upper case versions of the last_name field in the index. Another option sometimes supported is the use of “filtered” indexes, where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing on user-defined functions, as well as expression formed from an assortment of built-in functions.
Indexes may be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing duplicate entries in the index and thus the backing table.
Index architectures can be classified as clustered or non-clustered. In non-clustered index architectures, the data is present in random order, but the logical ordering is specified by the index. In clustered index architectures, clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order.
In database management systems, contention refers to multiple processes or instances competing for access to the same index or data block at the same time. In general, this can be caused by very frequent index or table scans, or frequent updates. Concurrent statement executions by two or more instances may also lead to contention, and subsequently busy waiting for the process without the block. The following three solutions are commonly used to reduce contention. First, to reduce contention for table blocks due to delete, select, or update statements, reduce the number of rows per block. This can be done by using a smaller block size. Second, to reduce contention for table blocks due to insert statements, increase the number of freelists, or buffer frames. Third, to reduce contention for index blocks, the best strategy is to implement a Reverse index. The goal of all three solutions is to spread queries over a greater number of blocks to avoid concentrating on any single one.